# -*- coding = utf-8 -*-
# @Time : 2021/5/25 15:36
# @Author : Yi Wen
# @File : sys_mysql.py
# @Software : PyCharm
import traceback
import time

import pymysql
from applications.configs import sys_config

from DBUtils.PooledDB import PooledDB


class MysqlHandler(object):

    __config = {
        'creator': pymysql,  # 使用链接数据库的模块
        'mincached': 1,  # 最小空闲连接数1, 如果空闲连接数小于这个数，pool会创建一个新的连接
        'maxcached': 0,  # 最大空闲连接数, 如果空闲连接数大于这个数，pool会关闭空闲连接
        'maxusage': 100,  # 单个连接的最大允许复用次数,当达到最大数时,连接会自动重新连接(关闭和重新打开)
        'autocommit': False,  # 是否自动提交事物
        'blocking': True,  # 当连接数达到最大的连接数时，在请求连接的时候，如果这个值是True，请求连接的程序会一直等待，直到当前连接数小于最大连接数，
        'host': sys_config.MYSQL_HOST,
        'port':  sys_config.MYSQL_PORT,
        'user':  sys_config.MYSQL_USERNAME,
        'password':  sys_config.MYSQL_PASSWORD,
        'db':  sys_config.MYSQL_DATABASE,
        'charset': 'utf8',
        'cursorclass': pymysql.cursors.DictCursor
    }

    def get_connect(self):
        """
        取连接池
        :return: 连接池对象
        """
        try:
            self.__pool = PooledDB(**self.__config)
            return self.__pool.connection()
        except Exception as e:
            traceback.print_exc()
            print('链接数据库池错误:', e)
            # self.__pool = PooledDB(**self.__config)
            # return self.__pool.connection()

    def __enter__(self):
        # 连接数据库
        self.conn = self.get_connect()
        self.cursor, self.conn = self.conn.cursor(), self.conn
        return self

    def select_one(self, select_sql: str, args: tuple = None) -> dict:
        self.cursor.execute(select_sql, args)
        data = self.cursor.fetchone()
        if not data:
            return {}
        return data

    def select_many(self, select_sql: str, args: tuple = None) -> list:
        self.cursor.execute(select_sql, args)
        data = self.cursor.fetchall()
        if not data:
            return []
        return data

    def close(self):
        self.cursor.close()
        self.conn.close()

    def __exit__(self, exc_type, exc_val, exc_tb):
        self.close()

    def delete(self, table, range_str):
        sql = 'DELETE FROM ' + table + ' WHERE ' + range_str
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            return cursor.rowcount
        except pymysql.Error as e:
            print(e)
            self.conn.rollback()

    def query(self, table, str_list: list, where_info: str):
        """
        封装查询与语句
        :param table: 要查询的表
        :param str_list: 查询的字段 例：["name","age"]
        :param where_info: 查询条件
        :return: 查询命中返回字段信息，未命中返回None，数据库查询错误返回False
        """

        sql = f"""select {",".join(str_list)} from {table} where {where_info}"""
        try:
            self.cursor.execute(sql)
            return self.cursor.fetchone()
        except pymysql.Error as e:
            return False

    def native_update(self, sql):
        """
        :param sql:  原生sql
        :return:  成功返回成功对象 失败返回False
        """

        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            return cursor.rowcount
        except pymysql.Error as e:
            print(e)

    def native_query(self, sql: str, many: bool = False):
        """
        原生语句查询
        :param sql: 原生语句
        :param many: 是否开启查询多条  默认False-不开启
        :return: 查询命中返回字段信息，未命中返回None，数据库查询错误返回False
        """
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            if many is False:
                return cursor.fetchone()
            return cursor.fetchall()
        except pymysql.Error as e:
            return False

    def query_all(self, table, str_list: list, where_info: str):
        """
        封装查询与语句
        :param table: 要查询的表
        :param str_list: 查询的字段 例：["name","age"]
        :param where_info: 查询条件
        :return: 查询命中返回字段信息，未命中返回None，数据库查询错误返回False
        """
        sql = f"""select {",".join(str_list)} from {table} where {where_info}"""
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            return cursor.fetchall()
        except pymysql.Error as e:
            return False

    def update(self, table, val_obj, range_str):
        """
        :param table:  需要更新的表
        :param val_obj:  需要更新的字段和值（键值对）
        :param range_str:  更新所需要的条件
        :return:  成功返回成功对象 失败返回False
        """
        sql = 'UPDATE ' + table + ' SET '
        for key in val_obj:
            sql += f'{key}={val_obj[key]},' if isinstance({val_obj[key]}, int) else f'{key}="{val_obj[key]}",'
        sql = sql[:-1] + ' WHERE ' + range_str
        try:
            # cursor = self.conn.cursor()
            self.cursor.execute(sql)
            return self.cursor.rowcount
        except pymysql.Error as e:
            print(e)
            self.conn.rollback()

    # 插入数据到数据表
    def insert(self, table, val_obj):
        """

        :param table: 表
        :param val_obj: kv值-k:数据库字段名 v：数据库字段值
        :return:
        """
        sql_top = 'INSERT INTO ' + table + ' ('
        sql_tail = ') VALUES ('
        for key, val in val_obj.items():
            if val_obj[key] is not None:
                sql_top += key + ','
                sql_tail += val + ',' if isinstance({val_obj[key]}, int) else f'"{val}",'
        sql = sql_top[:-1] + sql_tail[:-1] + ')'
        try:
            cursor = self.conn.cursor()
            cursor.execute(sql)
            return True
        except pymysql.Error as e:
            print(e)
            self.conn.rollback()
